package day.statistic;

import entity.day.DistOD;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utility.database.SqlServerAccess;
import utility.time.ProcessTime;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Created by lit on 2018/4/13.
 */
public class ProcessStatDistOD {
    //数据连接
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;

    private Logger logger = null;

    private boolean initMSSQL(){
        if( connection == null ){
            try {
                connection = SqlServerAccess.getInstance();
                connection.setAutoCommit(false);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private void finishMSSQL(){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void processDistOD(String[] dayStatTime){
        logger = LoggerFactory.getLogger(ProcessStatDistOD.class);
        /*String latestTime = getLatestTime();*/
        clearDistOD(dayStatTime);
        ArrayList<DistOD> distODArray = getDistOD(dayStatTime);
        addDistOD(distODArray);
        finishMSSQL();
    }

    private void clearDistOD(String[] dayStatTime) {
        if(initMSSQL()){
            String sql = "DELETE FROM stat_day_distOD where StatDate = ?";
            try{
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,dayStatTime[0]);
                int num = preparedStatement.executeUpdate();
                connection.commit();
                if(num > 0){
                    logger.info("清除{}重复数据{}条",dayStatTime[0],num);
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }

    /**
     * 数据插入stat_day_distOD数据表
     * @param distODArray
     */
    private void addDistOD(ArrayList<DistOD> distODArray) {
        if(initMSSQL()){
            String sql = "insert into stat_day_distOD (StatDate,DistType,OriginDistID,DesDistID,DailyNum,AmPeakNum,PmPeakNum) values (?,?,?,?,?,?,?)";
            try{
                int num = 0;
                preparedStatement = connection.prepareStatement(sql);
                for(int i = 0; i < distODArray.size(); i++){
                    DistOD distOD = distODArray.get(i);
                    preparedStatement.setString(1,distOD.getStatDate());
                    preparedStatement.setInt(2,distOD.getDistType());
                    preparedStatement.setInt(3,distOD.getOriginDistID());
                    preparedStatement.setInt(4,distOD.getDesDistID());
                    preparedStatement.setInt(5,distOD.getDailyNum());
                    preparedStatement.setInt(6,distOD.getAmPeakNum());
                    preparedStatement.setInt(7,distOD.getPmPeakNum());
                    preparedStatement.addBatch();
                    if((i+1) % 100 == 0){
                        preparedStatement.executeBatch();
                    }
                    num++;
                }
                preparedStatement.executeBatch();
                connection.commit();
                logger.info("区域OD插入数据{}条",num);
            }catch (Exception e){
                e.printStackTrace();
            }
        }

    }

    /**
     * 2.获取区域OD的统计数据
     * @param dayStatTime
     * @return
     */
    private ArrayList<DistOD> getDistOD(String[] dayStatTime) {
        if(initMSSQL()){
            logger.info("获取区域OD统计数据");
            ArrayList<DistOD> distODArray = new ArrayList<>();
            //行政区统计sql
            String districtSql = "SELECT OriginDistID,DesDistID,DailyNum,AmPeakNum,PmPeakNum,daily.tripTime FROM";
            //全天
            districtSql += " (SELECT CONVERT(VARCHAR(10),TripStartTime,21) as tripTime,StartDist OriginDistID,EndDist DesDistID,COUNT (*) DailyNum FROM rtm_trip " +
                    " WHERE TripStartTime >= ? AND TripStartTime < ? AND StartDist IS NOT NULL AND EndDist IS NOT NULL" +
                    " GROUP BY StartDist,EndDist,CONVERT(VARCHAR(10),TripStartTime,21)" +
                    " ) daily";

            // 晚高峰
            districtSql += " LEFT JOIN (" +
                    " SELECT CONVERT(VARCHAR(10),TripStartTime,21) as tripTime,StartDist pms,EndDist pme,COUNT (*) PmPeakNum FROM rtm_trip " +
                    " WHERE TripStartTime >= ? AND TripStartTime < ? AND DATENAME(HOUR, TripStartTime) >= 17 AND DATENAME(HOUR, TripStartTime) <= 19 AND StartDist IS NOT NULL AND EndDist IS NOT NULL " +
                    " GROUP BY StartDist, EndDist,CONVERT(VARCHAR(10),TripStartTime,21)" +
                    ") pm ON daily.OriginDistID = pm.pms AND daily.DesDistID = pm.pme AND daily.tripTime = pm.tripTime";
            //早高峰
            districtSql += " LEFT JOIN (" +
                    " SELECT CONVERT(VARCHAR(10),TripEndTime,21) as tripTime,StartDist ams,EndDist ame,COUNT (*) AmPeakNum " +
                    " FROM rtm_trip " +
                    " WHERE TripEndTime >= ? AND TripEndTime < ? AND DATENAME(HOUR, TripEndTime) >= 7 AND DATENAME(HOUR, TripEndTime) < 9" +
                    " AND StartDist IS NOT NULL AND EndDist IS NOT NULL " +
                    " GROUP BY StartDist,EndDist,CONVERT(VARCHAR(10),TripEndTime,21)" +
                    ") am ON daily.OriginDistID = am.ams AND daily.DesDistID = am.ame AND daily.tripTime = am.tripTime";

            //街道的统计sql
            String streetSql = "SELECT daily.tripTime,OriginDistID,DesDistID,DailyNum,AmPeakNum,PmPeakNum FROM ";
            streetSql += "(SELECT CONVERT(VARCHAR(10),TripStartTime,21) as tripTime,SUBSTRING(StartPOI,15,4) OriginDistID,SUBSTRING(EndPOI,15,4) DesDistID,count(*) DailyNum  from rtm_trip " +
                    " where TripStartTime>=? and TripStartTime<? and StartPOI is not null and EndPOI is not null " +
                    " GROUP BY SUBSTRING(StartPOI,15,4),SUBSTRING(EndPOI,15,4),CONVERT(VARCHAR(10),TripStartTime,21)) daily";
            streetSql += " LEFT JOIN" +
                    " (SELECT CONVERT(VARCHAR(10),TripStartTime,21) as tripTime,SUBSTRING(StartPOI,15,4) pms,SUBSTRING(EndPOI,15,4) pme,count(*) PmPeakNum from rtm_trip" +
                    " where TripStartTime>=? and TripStartTime< ? and StartPOI is not null and EndPOI is not null " +
                    " AND DATENAME(HOUR, TripStartTime) >= 17 AND DATENAME(HOUR, TripStartTime) <= 19" +
                    " GROUP BY SUBSTRING(StartPOI,15,4),SUBSTRING(EndPOI,15,4),CONVERT(VARCHAR(10),TripStartTime,21)) pm " +
                    " on daily.OriginDistID=pm.pms and daily.DesDistID=pm.pme and daily.tripTime = pm.tripTime";
            streetSql += " LEFT JOIN " +
                    "(SELECT CONVERT(VARCHAR(10),TripEndTime,21) as tripTime,SUBSTRING(StartPOI,15,4) ams,SUBSTRING(EndPOI,15,4) ame,count(*) AmPeakNum from rtm_trip " +
                    "where TripEndTime>=? and TripEndTime< ? and StartPOI is not null and EndPOI is not null " +
                    "AND DATENAME(HOUR, TripEndTime) >=  7 AND DATENAME(HOUR, TripEndTime) <= 9 " +
                    "GROUP BY SUBSTRING(StartPOI,15,4),SUBSTRING(EndPOI,15,4),CONVERT(VARCHAR(10),TripEndTime,21)) am " +
                    "on daily.OriginDistID=am.ams AND daily.DesDistID=am.ame  AND daily.tripTime = am.tripTime";
            int districtDistType = 0;
            int streetDistType= 1;

            getDistrictStreetDistOD(districtSql,distODArray,districtDistType,dayStatTime);
            getDistrictStreetDistOD(streetSql,distODArray,streetDistType,dayStatTime);

            return distODArray;
        }
        return null;
    }

    /**
     * @param sql
     * @param distODArray
     * @param distType
     * @param dayStatTime
     */
    private void getDistrictStreetDistOD(String sql, ArrayList<DistOD> distODArray, int distType, String[] dayStatTime) {
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,dayStatTime[0]);
            preparedStatement.setString(2,dayStatTime[1]);
            preparedStatement.setString(3,dayStatTime[0]);
            preparedStatement.setString(4,dayStatTime[1]);
            preparedStatement.setString(5,dayStatTime[0]);
            preparedStatement.setString(6,dayStatTime[1]);
            ResultSet rs = preparedStatement.executeQuery();
            while(rs.next()){
                DistOD distOD = new DistOD();
                distOD.setStatDate(rs.getString("tripTime"));
                distOD.setDistType(distType);
                distOD.setOriginDistID(rs.getInt("OriginDistID"));
                distOD.setDesDistID(rs.getInt("DesDistID"));
                distOD.setDailyNum(rs.getInt("DailyNum"));
                distOD.setAmPeakNum(rs.getInt("AmPeakNum"));
                distOD.setPmPeakNum(rs.getInt("PmPeakNum"));
                distODArray.add(distOD);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 1.获取待处理的日期
     * @return
     */
    private String getLatestTime() {
        if(initMSSQL()){
            String sql = "SELECT TOP 1 DATEADD(DAY,1,StatDate) as latestTime FROM stat_day_distOD ORDER BY StatDate DESC";
            try{
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
                if(rs.next()){
                    return rs.getString("latestTime");
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        return null;
    }
}
